Slave SQL线程与PXB FTWRL死锁问题分析
* GreatSQL社区原创内容未经授权不得随意使用，转载请联系小编并注明来源。
1. 问题背景
2.27号凌晨生产环境MySQL备库在执行备份期间出现因FLUSH TABLES WITH READ LOCK未释放导致备库复制延时拉大，慢日志内看持锁接近25分钟未释放。
版本：
MySQL 5.7.21 PXB 2.4.18
慢查询日志：
备份脚本中的备份命令：
mysql_kill.sh的主要逻辑内容：
备份参数：
2. 问题复现及分析
2.1 问题分析
144是SQL线程，并行复制中的Coordinator线程； 145/146是并行复制的worker线程，145/146worker线程队列中的事务可以并行执行。 162线程是执行innobackup执行的flush tables with read lock;
144 Coordinator线程分发relay log中事务时发现这个事务不能执行，要等待前面的事务完成提交，所以处于waiting for dependent transaction to commit的状态。145/146线程和备份线程162形成死锁，145线程等待162线程 global read lock 释放，162线程占有MDL::global read lock 全局读锁，申请全局commit lock的时候阻塞等待146线程，146线程占有MDL:: commit lock，因为从库设置slave_preserve_commit_order=1，保证从库binlog提交顺序，而146线程执行事务对应的binlog靠后面，所以等待145的事务提交。最终形成了145->162->146->145的死循环，形成死锁。
三个线程相互形成死锁，还是很少见的。
2.2 相关参数为何未生效
--ftwrl-wait-timeout=60 指的是执行FTWRL之前，如果检测到存在长SQL，先等待指定时间（秒），如果超时后还存在长SQL，则备份报错退出。默认为0则表示立即执行。
--ftwrl-wait-threshold=5 指的是执行FTWRL之前，检测长SQL的方法，如果在执行flush前存在已经运行了超过指定时间（秒）的SQL，则将该SQL定义为长SQL，默认60s。
--kill-long-queries_timeout=0 在执行FTWRL后，如果flush操作被阻塞了N秒，则kill掉阻塞它的线程，默认0的情况就是不kill任何阻塞flush的SQL，直到该SQL执行完成。
从上面各个参数的解释，不难看出，--ftwrl-wait-*参数是针对执行FTWRL之前的长SQL检测机制，对于已执行FTWRL时无济于事，--kill-long-*参数则是设置默认值0，不起任何作用。
3. 结论与建议
PXB备份中执行FTWRL加全局读锁与SQL线程形成死锁是导致本次从库延迟过高的原因。 启用--kill-long-queries_type和--kill-long-queries_timeout参数，在检测到flush被阻塞后执行kill掉相关线程的操作。比较暴力，存在较大的风险，若备库无业务访问则可考虑。 启用--safe-slave-backup参数，执行备份时该参数会停掉SQL线程，从而避免死锁的产生。仅建议在无业务访问的备库上执行。 设置MySQL参数slave_preserve_commit_order=0，关闭从库binlog的顺序提交，关闭该参数只是影响并行复制的事务在从库的提交顺序，对最终的数据一致性并无影响，所以如果无特别要求从库的binlog顺序必须与主库保持一致，可以考虑设置slave_preserve_commit_order=0避免死锁的产生。
1. 问题背景
2.27号凌晨生产环境MySQL备库在执行备份期间出现因FLUSH TABLES WITH READ LOCK未释放导致备库复制延时拉大，慢日志内看持锁接近25分钟未释放。
版本：
MySQL 5.7.21 PXB 2.4.18
慢查询日志：
备份脚本中的备份命令：
mysql_kill.sh的主要逻辑内容：
备份参数：
2. 问题复现及分析
2.1 问题分析
144是SQL线程，并行复制中的Coordinator线程； 145/146是并行复制的worker线程，145/146worker线程队列中的事务可以并行执行。 162线程是执行innobackup执行的flush tables with read lock;
144 Coordinator线程分发relay log中事务时发现这个事务不能执行，要等待前面的事务完成提交，所以处于waiting for dependent transaction to commit的状态。145/146线程和备份线程162形成死锁，145线程等待162线程 global read lock 释放，162线程占有MDL::global read lock 全局读锁，申请全局commit lock的时候阻塞等待146线程，146线程占有MDL:: commit lock，因为从库设置slave_preserve_commit_order=1，保证从库binlog提交顺序，而146线程执行事务对应的binlog靠后面，所以等待145的事务提交。最终形成了145->162->146->145的死循环，形成死锁。
三个线程相互形成死锁，还是很少见的。
2.2 相关参数为何未生效
--ftwrl-wait-timeout=60 指的是执行FTWRL之前，如果检测到存在长SQL，先等待指定时间（秒），如果超时后还存在长SQL，则备份报错退出。默认为0则表示立即执行。
--ftwrl-wait-threshold=5 指的是执行FTWRL之前，检测长SQL的方法，如果在执行flush前存在已经运行了超过指定时间（秒）的SQL，则将该SQL定义为长SQL，默认60s。
--kill-long-queries_timeout=0 在执行FTWRL后，如果flush操作被阻塞了N秒，则kill掉阻塞它的线程，默认0的情况就是不kill任何阻塞flush的SQL，直到该SQL执行完成。
从上面各个参数的解释，不难看出，--ftwrl-wait-*参数是针对执行FTWRL之前的长SQL检测机制，对于已执行FTWRL时无济于事，--kill-long-*参数则是设置默认值0，不起任何作用。
3. 结论与建议
PXB备份中执行FTWRL加全局读锁与SQL线程形成死锁是导致本次从库延迟过高的原因。 启用--kill-long-queries_type和--kill-long-queries_timeout参数，在检测到flush被阻塞后执行kill掉相关线程的操作。比较暴力，存在较大的风险，若备库无业务访问则可考虑。 启用--safe-slave-backup参数，执行备份时该参数会停掉SQL线程，从而避免死锁的产生。仅建议在无业务访问的备库上执行。 设置MySQL参数slave_preserve_commit_order=0，关闭从库binlog的顺序提交，关闭该参数只是影响并行复制的事务在从库的提交顺序，对最终的数据一致性并无影响，所以如果无特别要求从库的binlog顺序必须与主库保持一致，可以考虑设置slave_preserve_commit_order=0避免死锁的产生。
《深入浅出MGR》视频课程
戳此小程序即可直达B站
https://www.bilibili.com/medialist/play/1363850082?business=space_collection&business_id=343928&desc=0
文章推荐：
想看更多技术好文，点个“在看”吧